/*
stat_weizhan_bd   微站数据统计
call proc_stat_jiance_data_hour ('2021-10-13 10:00:00');
call proc_stat_jiance_data_day	('2021-10-11 03:00:00');

pro_stat_jiance_data_hour	1、根据road_bd_hourdata 和 site_bd_hourdata 数据表，生成 t_d_jiance_hour 和 t_d_weizhan_hour
pro_stat_jiance_data_day	1、根据road_bd_daydata_cal 和 site_bd_daydata_cal 数据表，生成 t_d_jiance_day 和 t_d_weizhan_day

		# 指标对应关系
		### 微站&监测点 
		select * from road_bd_device order by id desc limit 100;
		select * from road_bd_contam;
		select * from road_bd_device_contam ;
		select * from road_bd_hourdata limit 100;
		select * from t_d_jiance_hour;
		select * from t_d_weizhan_hour;
		select r.PostTime,r.MN,r.pID,r.AvgValue,d.LNG as d_LNG,d.LAT as d_LAT,concat(TRUNCATE(d.LNG,3),'_',TRUNCATE(d.LAT,3)) as gid_100,bdc.pName,r.AvgValue 
				from road_bd_hourdata r right join 
					(select MN,LNG,LAT,ID from road_bd_device bd where ProjectType=5 and ID in (select max(ID) from road_bd_device group by MN) ) d on r.MN=d.MN and r.MN is not null 
					left join road_bd_contam bdc on bdc.pCode=r.pID 
				where r.PostTime='2021-08-01 00:00:00' and locate(bdc.pName,'PM2.5,PM10,CO浓度值,NO浓度值,NO2浓度值,O3')
				order by r.PostTime asc,r.MN asc, r.pID asc; 
		# 监测点
		select * from t_d_jiance_hour limit 100;
		
		select r.PostTime,r.MN,d.SiteName,d.LNG as d_LNG,d.LAT as d_LAT,
				MAX(case r.pID when '01' then  AvgValue end) as tsp, 
				MAX(case r.pID when '01A' then  AvgValue end) as PM25,
				MAX(case r.pID when '01B' then  AvgValue end) as PM10,
				MAX(case r.pID when 'CO' then  AvgValue end) as CO,
				MAX(case r.pID when 'NO' then  AvgValue end) as `NO`,
				MAX(case r.pID when 'NO2' then  AvgValue end) as NO2
				from road_bd_hourdata r right join 
					(select MN,LNG,LAT,ID,SiteName from road_bd_device bd where ProjectType=5 and ID in (select max(ID) from road_bd_device group by MN) ) d
					on r.MN=d.MN and r.MN is not null 
				where r.PostTime='2021-08-01 00:00:00' and locate(r.pID,'01,01A,01B,CO,NO,NO2') 
				group by r.PostTime,r.MN,d.SiteName,d.LNG,d.LAT 
				order by r.PostTime asc,r.MN asc, r.pID asc; 
		delete from road_bd_hourdata where PostTime is not null;
				
		select * from t_d_jiance_hour order by id desc limit 100;		
		select * from t_d_weizhan_hour order by id desc limit 100;		
		
		round((round(avg(NO),0)*2.05/1.34+round(avg(NO2),0)),2) as NOX 
*/
-- -------------------------------------

DELIMITER // 
DROP PROCEDURE IF EXISTS `proc_stat_jiance_data_hour`;
CREATE PROCEDURE `proc_stat_jiance_data_hour`(in start_time_ varchar(40))
BEGIN

		DECLARE hour_start_ datetime DEFAULT DATE_SUB(now(),INTERVAL 1 HOUR);	
		DECLARE hour_end_ datetime DEFAULT DATE_SUB(now(),INTERVAL 1 HOUR);
		DECLARE has_weizhan_data_ int DEFAULT 0;
		DECLARE has_site_data_ int DEFAULT 0;

		#DECLARE start_time_ datetime DEFAULT now();	
		if (SELECT DATE_FORMAT(start_time_,'%Y-%m-%d %H:%i:%s')) IS NULL then 
			set start_time_ = now();
		end if;
		select DATE_FORMAT(start_time_,'%Y-%m-%d %H:00:00') into start_time_;
		set hour_start_ = start_time_;
		set hour_end_ = DATE_ADD(hour_start_,INTERVAL 1 HOUR);	
		
		select 1 into has_weizhan_data_ from t_d_weizhan_hour where PostTime=hour_start_ limit 1;
		select 1 into has_site_data_ from t_d_jiance_hour where PostTime=hour_start_ limit 1;
		
		
		IF has_weizhan_data_=0 and now()>start_time_ THEN
				#微站数据行转列
				insert into t_d_weizhan_hour (PostTime,MN,SiteName,lon,lat,PM25,PM10,O3,CO,`NO`,NO2,SO2) 
					select r.PostTime,r.MN,d.SiteName,d.LNG as d_LNG,d.LAT as d_LAT,
							#MAX(case r.pID when '01' then  AvgValue end) as tsp, 
							MAX(case r.pID when 'PM25' then  AvgValue end) as PM25,
							MAX(case r.pID when 'PM10' then  AvgValue end) as PM10,
							MAX(case r.pID when 'O3' then  AvgValue end) as O3,
							MAX(case r.pID when 'CO' then  AvgValue end) as CO,
							MAX(case r.pID when 'NO' then  AvgValue end) as `NO`,
							MAX(case r.pID when 'NO2' then  AvgValue end) as NO2,
							MAX(case r.pID when 'SO2' then  AvgValue end) as SO2							
							from road_bd_hourdata r right join 
								#(select MN,LNG,LAT,ID,SiteName from road_bd_device bd where ProjectType=5 and ID in (select max(ID) from road_bd_device group by MN) ) d
								(select MN,LNG,LAT,ID,SiteName from road_bd_device bd where ProjectType=5 and devtype=4 and mn like 'SXYD0%') d
								on r.MN=d.MN and r.MN is not null 
							where r.PostTime>=hour_start_ and r.PostTime<hour_end_ and locate(r.pID,'01,PM25,PM10,O3,CO,NO,NO2,SO2') 
							group by r.PostTime,r.MN,d.SiteName,d.LNG,d.LAT 
							order by r.PostTime asc,r.MN asc, r.pID asc; 
				# 更新NOX指标
				update t_d_weizhan_hour set NOX=round((NO*2.05/1.34+NO2),2) where NOX is NULL;
		END IF;
-- 		select * from t_d_jiance_hour limit 100;
-- 		select * from site_bd_hourdata  limit 100;
-- 		select * from site_bd_device limit 100;
		IF has_site_data_=0 and now()>start_time_ THEN
				# 监测点数据行转列
				insert into t_d_jiance_hour (PostTime,MN,SiteName,lon,lat,tsp) 
					select r.PostTime,r.MN,d.SiteName,d.LNG as d_LNG,d.LAT as d_LAT,
							MAX(case r.pID when '01' then  AvgValue end) as tsp 
							from road_bd_hourdata r right join 
								#(select MN,LNG,LAT,ID,SiteName from road_bd_device bd where ProjectType<>5 and ID in (select max(ID) from road_bd_device group by MN) ) d
								(select MN,LNG,LAT,ID,SiteName from road_bd_device bd where ProjectType<>5 and devtype=4) d
								on r.MN=d.MN and r.MN is not null 
							where r.PostTime>=hour_start_ and r.PostTime<hour_end_ and locate(r.pID,'01') 
							group by r.PostTime,r.MN,d.SiteName,d.LNG,d.LAT 
							order by r.PostTime asc,r.MN asc, r.pID asc; 
				# 监测点数据行转列
				insert into t_d_jiance_hour (PostTime,MN,SiteName,lon,lat,tsp) 
					select r.PostTime,r.MN,d.SiteName,d.LNG as d_LNG,d.LAT as d_LAT,
							MAX(case r.pID when '01' then  AvgValue end) as tsp 
							from site_bd_hourdata r right join 
								#(select MN,LNG,LAT,ID,SiteName from site_bd_device bd where ID in (select max(ID) from site_bd_device group by MN) ) d
								(select MN,LNG,LAT,ID,SiteName from site_bd_device bd where devtype=4) d
								on r.MN=d.MN and r.MN is not null 
							where r.PostTime>=hour_start_ and r.PostTime<hour_end_ and locate(r.pID,'01') 
							group by r.PostTime,r.MN,d.SiteName,d.LNG,d.LAT 
							order by r.PostTime asc,r.MN asc, r.pID asc; 
		END IF;

END
//
DELIMITER ;